import pandas as pd
from datetime import date,timedelta

#加月份的小算法
def add_month(d,md):
    yd=md//12
    m=d.month+md%12
    if m!=12:
        yd+=m//12
        m=m%12
    return date(d.year+yd,m,d.day)


#skiprows跳过空行
#usecols="C:F" 跳过空列的处理方法
books=pd.read_excel('Books1.xlsx',skiprows=3,usecols="C:F",
                    index_col=None,dtype={'ID':str,'InStore':str,'Date':str})
print(books['ID'])
print(type(books['ID']))
# 填充series
# books['ID'].at[0]=100
#nan 默认为float

start=date(2020,6,6)
for i in books.index:
    '''
    #先拿series,再改series里面的值
    books['ID'].at[i]=i+1
    books['InStore'].at[i]='Yes' if i%2==0 else 'No'
    #日期上加一天
    #books['Date'].at[i]=start+timedelta(days=i)
    # timedelta(hours=)
    # timedelta(minutes=)
    #加一年
    #books['Date'].at[i]=date(start.year+i,start.month,start.day)
    # 加月份
    books['Date'].at[i] = add_month(start,i)
    '''
    #直接在dateframe里面修改
    books.at[i,'ID']=i+1
    books.at[i,'InStore']='Yes' if i%2==0 else 'No'
    # 加月份
    books.at[i,'Date'] = add_month(start, i)

books.set_index('ID',inplace=True)
books.to_excel('Output.xlsx')
print(books)

